﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace AnsQ.Helper
{
    public  class TSQLHelper
    {
        /// <summary>
        /// 查询方法（无参）
        /// </summary>
        /// <param name="sqltext">查询语句</param>
        /// <returns>数据表格</returns>
        public static DataTable GetDataTableBySQL(string sqltext)
        {
            string connstr = ConfigurationManager.ConnectionStrings["AnsTSQL"].ConnectionString;
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            sqlDataAdapter.Fill(table);
            return table;
        }

        /// <summary>
        /// 查询方法（含参）
        /// </summary>
        /// <param name="sqltext">查询语句</param>
        /// <param name="P">参数</param>
        /// <param name="V">值</param>
        /// <returns>数据表格</returns>
        public static DataTable GetDataTableBySQL(string sqltext,string [] P ,string [] V)
        {
            string connstr = ConfigurationManager.ConnectionStrings["AnsTSQL"].ConnectionString;
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            if (P!=null)
            {
                for (int i = 0; i < P.Length; i++)
                {
                    cmd.Parameters.AddWithValue(P[i], V[i]);
                }
            }
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            sqlDataAdapter.Fill(table);
            return table;
        }


        /// <summary>
        /// 插入/更新数据
        /// </summary>
        /// <param name="sqltext">查询语句</param>
        /// <param name="P">参数</param>
        /// <param name="V">参数值</param>
        /// <returns></returns>
        public static bool InsertOrUpdateSQL(string sqltext, string[] P, string[] V)
        {
            string connstr = ConfigurationManager.ConnectionStrings["AnsTSQL"].ConnectionString;
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            if (P!=null)
            {
                for (int i = 0; i < P.Length; i++)
                {
                    cmd.Parameters.AddWithValue(P[i], V[i]);
                }
            }
            conn.Open();
            int res=cmd.ExecuteNonQuery();
            conn.Close();
            if (res>0)
            {
                return true;
            }
            return false;
        }


        public static bool DoRemove(string sqltext, string[] P, string[] V)
        {
            string connstr = ConfigurationManager.ConnectionStrings["AnsTSQL"].ConnectionString;
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sqltext, conn);
            if (P != null)
            {
                for (int i = 0; i < P.Length; i++)
                {
                    cmd.Parameters.AddWithValue(P[i], V[i]);
                }
            }
            conn.Open();
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res > 0)
            {
                return true;
            }
            return false;
        }

    }
}